<?php defined('SYSPATH') or die('No direct script access.');
/**
 * Created by PhpStorm.
 * User: locove
 * Date: 15-7-22
 * Time: 下午2:36
 * 肖总要数据临时的一个文件导出所有SKU最近十次采购明细
 */
class Controller_Report_Nearpurchase extends Controller_Admin{
    /**
     * 首页
     */
    public function action_index()
    {
        $this->title='SKU最近十次采购明细下载';
        $view=View::factory('report/nearpurchase');
        if(isset($_GET['errors'])){
            $view->set('errors',$_GET['errors']);
        }
        $this->response->body($view);
    }
    /**
     * 下载模板
     */
    public function action_down_template()
    {
        $this->title='下载模版';
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("newerp")
            ->setLastModifiedBy("newerp")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objPHPExcel->getActiveSheet()->setTitle('load');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'SKU模板.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    public function getSql($fu) {
        return "SELECT purchaseorder_details.`sku`
            FROM `purchaseorder_details`
            JOIN purchaseorders ON purchaseorders.id = purchaseorder_details.`purchaseorder_id`
            WHERE purchaseorders.`status` !=100
            AND purchaseorder_details.`status` !=100
            GROUP BY purchaseorder_details.`sku`
            HAVING count( purchaseorder_details.`id` ) {$fu} 10";
    }

    public function action_downAll() {
        set_time_limit(0);
        ini_set('memory_limit','2048M');
        ini_set('max_allowed_packet','33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));

        /****************************获取大于10次的sku*******************************************/
        $bigs = DB::query(Database::SELECT, self::getSql('>'))->execute('newerp')->as_array(NULL,'sku');

        $bigs = "'".join("','", $bigs)."'";

        $sql = "SELECT `purchaseorder_details`.`id`,purchaseorder_details.`sku`
            FROM `purchaseorder_details`
            JOIN `purchaseorders` ON `purchaseorders`.`id` = `purchaseorder_details`.`purchaseorder_id`
            WHERE `purchaseorders`.`status` !=100
            AND `purchaseorder_details`.`status` !=100
            AND `purchaseorder_details`.`sku` IN ({$bigs})
            ORDER BY `purchaseorder_details`.`sku` DESC,`purchaseorder_details`.`id` DESC";

        $bigs = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        $big_ids = $ids = array();
        foreach($bigs as $big) {
            if(isset($big_ids[$big['sku']])) {
                if(count($big_ids[$big['sku']]) < 10) {
                    $ids[] = $big_ids[$big['sku']][] = $big['id'];
                }
            }else{
                $ids[] = $big_ids[$big['sku']][] = $big['id'];
            }
        }

        unset($big_ids);

        $ids = join(',' , $ids);
        $sql = "SELECT `pd`.`purchaseorder_id`,`pd`.`supplier`,`pd`.`sku`,`pd`.`sku_name`,`pd`.`quantity`,`pd`.`single_price`,`pd`.`details_date`,`p`.`purchase_id`
            FROM `purchaseorder_details` as `pd`
            JOIN `purchaseorders` as `p` ON `p`.`id` = `pd`.`purchaseorder_id`
            WHERE `pd`.`id` IN ({$ids})
            ORDER BY `pd`.`sku` ASC,`pd`.`purchaseorder_id` DESC";
        $res = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();


        /******************************小于等于10次的sku******************************/
        $bigs = DB::query(Database::SELECT, self::getSql('<='))->execute('newerp')->as_array(NULL,'sku');

        $bigs = "'".join("','", $bigs)."'";

        $sql = "SELECT `purchaseorder_details`.`id`
            FROM `purchaseorder_details`
            JOIN `purchaseorders` ON `purchaseorders`.`id` = `purchaseorder_details`.`purchaseorder_id`
            WHERE `purchaseorders`.`status` !=100
            AND `purchaseorder_details`.`status` !=100
            AND `purchaseorder_details`.`sku` IN ({$bigs})
            ORDER BY `purchaseorder_details`.`sku` DESC,`purchaseorder_details`.`id` DESC";

        $bigs = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();

        $ids_str = '';
        foreach($bigs as $big) {
            $ids_str .= empty($ids_str)?$big['id']:','.$big['id'];
        }

        $sql = "SELECT `sku`, `sku_name`
            FROM `purchaseorder_details`
            WHERE `id` IN ({$ids_str})
            GROUP BY `sku`";
        $names = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array('sku', 'sku_name');

        $sql = "SELECT `purchaseorder_details`.`purchaseorder_id`,`purchaseorder_details`.`supplier`,`purchaseorder_details`.`sku`,`purchaseorder_details`.`quantity`,`purchaseorder_details`.`single_price`,`purchaseorder_details`.`details_date`, `purchaseorders`.`purchase_id`
            FROM `purchaseorder_details`
            JOIN `purchaseorders` ON `purchaseorders`.`id` = `purchaseorder_details`.`purchaseorder_id`
            WHERE `purchaseorder_details`.`id` IN ({$ids_str})
            ORDER BY `purchaseorder_details`.`sku` ASC,`purchaseorder_details`.`details_date` DESC";

        unset($bigs, $ids_str);

        $res = array_merge($res, DB::query(Database::SELECT, $sql)->execute('newerp')->as_array());

        unset($sql);

        $nicks = DB::select('id','nick')->from("users")->execute()->as_array('id', 'nick');

        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("YKS")
            ->setLastModifiedBy("YKS")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', '中文名称');
        $objActSheet->setCellValue('C1', '采购时间');
        $objActSheet->setCellValue('D1', '采购员');
        $objActSheet->setCellValue('E1', '单价');
        $objActSheet->setCellValue('F1', '数量');
        $objActSheet->setCellValue('G1', '采购单号');
        $objActSheet->setCellValue('H1', '供应商ID');

        foreach($res as $num => $re){
            $objActSheet->setCellValue('A'.(($num+2)), $re['sku']);
            $objActSheet->setCellValue('B'.($num+2), isset($re['sku_name'])?$re['sku_name']:$names[$re['sku']]);
            $objActSheet->setCellValue('C'.($num+2), $re['details_date']);
            $objActSheet->setCellValue('D'.($num+2), isset($nicks[$re['purchase_id']])?$nicks[$re['purchase_id']]:'');
            $objActSheet->setCellValue('E'.($num+2), $re['single_price']);
            $objActSheet->setCellValue('F'.($num+2), $re['quantity']);
            $objActSheet->setCellValue('G'.($num+2), $re['purchaseorder_id']);
            $objActSheet->setCellValue('H'.($num+2), $re['supplier']);
        }

        unset($names, $res);
        $objPHPExcel->getActiveSheet()->setTitle('SKU最近十次采购明细下载');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'SKU最近十次采购明细'.'.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    /**
     * 导入sku查询(暂时不做)
     */
    public function action_load()
    {
        set_time_limit(0);
        ini_set('memory_limit','1152M');
        ini_set('max_allowed_packet','33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $xls= $_FILES['xls']['tmp_name'];
        $objPHPExcel= PHPExcel_IOFactory::load($xls);
        $data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $num = count($data);
        $error_arr=array();//存储不合格的行
        $begin_time=time();
        $skus_execl=$skus_arr=$error_num=$sku_error_arr=array();

        $sku_str='';
        for($i=2;$i<=$num;$i++){
            //			$sku.=trim($data[$i]['A']);
            //			if(Deliverycheck::check_sku($sku)){
            //			}else{
            //				$error_arr[]=$i;
            //			}
            $skus_execl[$i-2]= strtoupper(trim($data[$i]['A']));
            $sku_str.=$sku_str==''?"'".strtoupper(trim($data[$i]['A']))."'":",'".strtoupper(trim($data[$i]['A']))."'";
        }
        if($sku_str!=''){
            //START     ----------------------------------CWY更新代码-------------------------------------
            $skus_arr = Skusystemapi::getSkuIn($sku_str);
            //			$sql="select upper(trim(products_sku)) as sku
            //				from erp_products_data
            //				where products_sku<>''
            //				and products_sku in (".$sku_str.")";
            //			$skus_arr=DB::query(Database::SELECT,$sql)->execute('readonlyerp')->as_array();
            //END     ----------------------------------CWY更新代码-------------------------------------
            if(!empty($skus_arr)){
                $values = array();

                foreach ($skus_arr as $row)
                {
                    if (isset($row['sku']))
                    {
                        $values[] = strtoupper($row['sku']);
                    }
                }

                $skus_arr =  $values;
                $sku_error_arr = array_diff($skus_execl,$skus_arr);
                if($sku_error_arr)
                {
                    $error_num = array_keys($sku_error_arr);
                }
            }
        }

        //		if(empty($error_num))
        //		{
        $daysales_all = $actual_num_all = $op_print_num_all = $onway_all = array();
        if($sku_str)
        {
            //实际库存信息
            $actual_num_all = Warningtmp::get_inventory($skus_arr);

            //订单占用库存信息
            $op_print_num_all = Warningtmp::get_print_num($skus_arr);


            //在途库存信息
            $onway_all = Warningtmp::get_onway_num($skus_arr);

            //销量信息
            $daysales_all_string = "SELECT sku,daysales,daysales7,daysales14,daysales28 FROM product_attached
				WHERE sku in(".$sku_str.")";

            $return = DB::query(Database::SELECT,$daysales_all_string)->execute('readonlyerp')->as_array();

            if($return)
            {
                foreach ($return as $value)
                {
                    $daysales_all[$value['sku']]['daysales'] = $value['daysales'];
                    $daysales_all[$value['sku']]['daysales7'] = $value['daysales7'];
                    $daysales_all[$value['sku']]['daysales14'] = $value['daysales14'];
                    $daysales_all[$value['sku']]['daysales28'] = $value['daysales28'];
                }
            }
        }
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("YKS")
            ->setLastModifiedBy("YKS")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objActSheet->setCellValue('B1', '中文名称');
        $objActSheet->setCellValue('C1', '采购时间');
        $objActSheet->setCellValue('D1', '采购员');
        $objActSheet->setCellValue('E1', '单价');
        $objActSheet->setCellValue('F1', '数量');
        $objActSheet->setCellValue('G1', '实际库存数量');
        $objActSheet->setCellValue('H1', '总未发数量');
        $objActSheet->setCellValue('I1', '库存可用数量');
        $objActSheet->setCellValue('J1', '总可用数量');

        foreach($skus_arr as $num => $sku){
            $objActSheet->setCellValue('A'.(($num+2)), $sku);
            $onway_temp=(isset($onway_all[$sku])&&$onway_all[$sku]>0)?$onway_all[$sku]:0;
            $objActSheet->setCellValue('B'.($num+2), $onway_temp);
            $objActSheet->setCellValue('C'.($num+2), isset($daysales_all[$sku]['daysales'])?$daysales_all[$sku]['daysales']:0);
            $objActSheet->setCellValue('D'.($num+2), isset($daysales_all[$sku]['daysales7'])?$daysales_all[$sku]['daysales7']:0);
            $objActSheet->setCellValue('E'.($num+2), isset($daysales_all[$sku]['daysales14'])?$daysales_all[$sku]['daysales14']:0);
            $objActSheet->setCellValue('F'.($num+2), isset($daysales_all[$sku]['daysales28'])?$daysales_all[$sku]['daysales28']:0);

            $actual_temp=isset($actual_num_all[$sku])?$actual_num_all[$sku]:0;
            $op_print_num_temp=isset($op_print_num_all[$sku])?$op_print_num_all[$sku]:0;
            $keyong=$actual_temp-$op_print_num_temp;
            $objActSheet->setCellValue('G'.($num+2), $actual_temp);
            $objActSheet->setCellValue('H'.($num+2), $op_print_num_temp);
            $objActSheet->setCellValue('I'.($num+2), $keyong);
            $objActSheet->setCellValue('J'.($num+2), $keyong+$onway_temp);

        }
        if($sku_error_arr){
            $error_num=0;
            $numbers=count($skus_arr)+2;
            $objActSheet->setCellValue('A'.$numbers, '以下SKU找不到或者找不到数据');
            $error_num++;
            foreach($sku_error_arr as $_sku){
                $objActSheet->setCellValue('A'.($numbers+$error_num), $_sku);
                $error_num++;
            }
        }
        $objPHPExcel->getActiveSheet()->setTitle('在途库存报表');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        $filename=$day.'在途库存报表'.'.xls';
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
        //		}else{
        //			//记录有误的sku
        //			$errors='';
        //			foreach ($error_num as $_error){
        //				$errors.=$errors==''?$_error:','.$_error;
        //			}
        //			$this->request->redirect('report/onway/index?errors='.$errors);
        //		}
    }
}
